﻿using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using Framework;

namespace Enterprise
{
    public class ProductService : BaseService
    {
        public int Edit(int id, string name, string nameEn, int productCategoryId, string description, string descriptionEn, string model, bool isRecommend, int coverId, string keyword = "", string keywordEn = "", string finish = "", string finishEn = "", string material = "", string materialEn = "", string doorThickness = "", string backset = "", string availableFor = "", string availableForEn = "", string applicableStandard = "", string size = "", string modelEn = "", string summary = "", string summaryEn = "")
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "产品名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "产品名称(En)");
            var sbSql = new StringBuilder();
            sbSql.Append("update tbproduct set ");
            sbSql.Append("CoName=@CoName");
            sbSql.Append(",");
            sbSql.Append("CoDescription=@CoDescription");
            sbSql.Append(",");
            sbSql.Append("CoProductCategoryId=@CoProductCategoryId");
            sbSql.Append(",");
            sbSql.Append("CoCategoryPath=@CoCategoryPath");
            sbSql.Append(",");
            sbSql.Append("CoModel=@CoModel");
            sbSql.Append(",");
            sbSql.Append("CoIsRecommend=@CoIsRecommend");
            sbSql.Append(",");
            sbSql.Append("CoCoverImageId=@CoCoverImageId");
            sbSql.Append(",");
            sbSql.Append("CoNormalImage=@CoNormalImage");
            sbSql.Append(",");
            sbSql.Append("CoSmallImage=@CoSmallImage");
            sbSql.Append(",");
            sbSql.Append("CoBigImage=@CoBigImage");
            sbSql.Append(",");
            sbSql.Append("CoNameEn=@CoNameEn");
            sbSql.Append(",");
            sbSql.Append("CoDescriptionEn=@CoDescriptionEn");
            sbSql.Append(",");
            sbSql.Append("CoKeyword=@CoKeyword");
            sbSql.Append(",");
            sbSql.Append("CoFinish=@CoFinish");
            sbSql.Append(",");
            sbSql.Append("CoMaterial=@CoMaterial");
            sbSql.Append(",");
            sbSql.Append("CoDoorThickness=@CoDoorThickness");
            sbSql.Append(",");
            sbSql.Append("CoBackset=@CoBackset");
            sbSql.Append(",");
            sbSql.Append("CoAvailableFor=@CoAvailableFor");
            sbSql.Append(",");
            sbSql.Append("CoApplicableStndard=@CoApplicableStndard");
            sbSql.Append(",");
            sbSql.Append("CoKeywordEn=@CoKeywordEn");
            sbSql.Append(",");
            sbSql.Append("CoFinishEn=@CoFinishEn");
            sbSql.Append(",");
            sbSql.Append("CoMaterialEn=@CoMaterialEn");
            sbSql.Append(",");
            sbSql.Append("CoAvailableForEn=@CoAvailableForEn");
            sbSql.Append(",");
            sbSql.Append("CoSize=@CoSize");
            sbSql.Append(",");
            sbSql.Append("CoModelEn=@CoModelEn");
            sbSql.Append(",");
            sbSql.Append("CoSummary=@CoSummary");
            sbSql.Append(",");
            sbSql.Append("CoSummaryEn=@CoSummaryEn");
            sbSql.AppendFormat(" where coproductId={0}", id);
            OleDbParameter[] parameters =
                {
                    new OleDbParameter("@CoName", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoDescription", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoProductCategoryId", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoCategoryPath", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoModel", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoIsRecommend", OleDbType.Boolean),
                    new OleDbParameter("@CoCoverImageId", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoNormalImage", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoSmallImage", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoBigImage", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoNameEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoDescriptionEn", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoKeyword", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoFinish", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoMaterial", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoDoorThickness", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoBackset", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoAvailableFor", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoApplicableStndard", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoKeywordEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoFinishEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoMaterialEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoAvailableForEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoSize", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoModelEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoSummary", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoSummaryEn", OleDbType.VarChar, 255)
                };
            parameters[0].Value = name;
            parameters[1].Value = description;
            using (var sdr = DbHelperOleDb.ExecuteReader("select * from tbproductcategory where CoproductCategoryId={0}".FormatWith(productCategoryId)))
            {
                if (sdr.Read())
                {
                    parameters[2].Value = productCategoryId;
                    parameters[3].Value = sdr["CoCategoryPath"].SafeToString();
                }
                else
                {
                    throw new Exception("分类不存在");
                }
            }
            parameters[4].Value = model;
            parameters[5].Value = isRecommend;
            using (var sdr = DbHelperOleDb.ExecuteReader("select * from TbProductImage where CoProductImageId={0}".FormatWith(coverId)))
            {

                if (sdr.Read())
                {
                    parameters[6].Value = coverId;
                    parameters[7].Value = sdr["CoNormalImage"].SafeToString();
                    parameters[8].Value = sdr["CoSmallImage"].SafeToString();
                    parameters[9].Value = sdr["CoBigImage"].SafeToString();
                }
                else
                {
                    parameters[6].Value = DBNull.Value;
                    parameters[7].Value = DBNull.Value;
                    parameters[8].Value = DBNull.Value;
                    parameters[9].Value = DBNull.Value;
                }
            }

            parameters[10].Value = nameEn;
            parameters[11].Value = descriptionEn;
            parameters[12].Value = keyword;
            parameters[13].Value = finish;
            parameters[14].Value = material;
            parameters[15].Value = doorThickness;
            parameters[16].Value = backset;
            parameters[17].Value = availableFor;
            parameters[18].Value = applicableStandard;
            parameters[19].Value = keywordEn;
            parameters[20].Value = finishEn;
            parameters[21].Value = materialEn;
            parameters[22].Value = availableForEn;
            parameters[23].Value = size;
            parameters[24].Value = modelEn;
            parameters[25].Value = summary;
            parameters[26].Value = summaryEn;
            var rows = DbHelperOleDb.ExecuteSql(sbSql.ToString(), parameters).SafeToInt();
            if (rows > 0)
            {
                DbHelperOleDb.ExecuteSqlTran(new ArrayList
                    {
                        "update TbProductImage set CoIsCover=0 where CoProductId={0} and CoIsCover=1".FormatWith(id),
                        "update TbProductImage set CoIsCover=1 where CoProductImageId={0}".FormatWith(coverId)

                    });
            }
            return rows;
        }
        public int Add(string name, string nameEn, int productCategoryId, string description, string descriptionEn, string model, bool isRecommend, int[] productImageIds, int coverId, string keyword = "", string keywordEn = "", string finish = "", string finishEn = "", string material = "", string materialEn = "", string doorThickness = "", string backset = "", string availableFor = "", string availableForEn = "", string applicableStandard = "", string size = "", string modelEn = "", string summary = "", string summaryEn = "")
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "产品名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "产品名称(En)");
            var strSqlOne = new StringBuilder();
            strSqlOne.Append("insert into tbproduct(");
            strSqlOne.Append("CoName,CoDescription,CoProductCategoryId,CoCategoryPath,CoModel,CoIsRecommend,CoCoverImageId,CoNormalImage,CoSmallImage,CoBigImage,CoNameEn,CoDescriptionEn,CoKeyword,CoFinish,CoMaterial,CoDoorThickness,CoBackset,CoAvailableFor,CoApplicableStndard,CoKeywordEn,CoFinishEn,CoMaterialEn,CoAvailableForEn,CoSize,CoModelEn,CoSummary,CoSummaryEn");
            strSqlOne.Append(")");
            strSqlOne.Append(" values (");
            strSqlOne.Append("@CoName,@CoDescription,@CoProductCategoryId,@CoCategoryPath,@CoModel,@CoIsRecommend,@CoCoverImageId,@CoNormalImage,@CoSmallImage,@CoBigImage,@CoNameEn,@CoDescriptionEn,@CoKeyword,@CoFinish,@CoMaterial,@CoDoorThickness,@CoBackset,@CoAvailableFor,@CoApplicableStndard,@CoKeywordEn,@CoFinishEn,@CoMaterialEn,@CoAvailableForEn,@CoSize,@CoModelEn,@CoSummary,@CoSummaryEn");
            strSqlOne.Append(")");
            OleDbParameter[] parametersOne =
                {
                    new OleDbParameter("@CoName", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoDescription", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoProductCategoryId", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoCategoryPath", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoModel", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoIsRecommend", OleDbType.Boolean),
                    new OleDbParameter("@CoCoverImageId", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoNormalImage", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoSmallImage", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoBigImage", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoNameEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoDescriptionEn", OleDbType.LongVarWChar),
                    new OleDbParameter("@CoKeyword", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoFinish", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoMaterial", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoDoorThickness", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoBackset", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoAvailableFor", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoApplicableStndard", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoKeywordEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoFinishEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoMaterialEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoAvailableForEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoSize", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoModelEn", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoSummary", OleDbType.VarChar, 255),
                    new OleDbParameter("@CoSummaryEn", OleDbType.VarChar, 255)

                };
            parametersOne[0].Value = name;
            parametersOne[1].Value = description;
            using (var sdr = DbHelperOleDb.ExecuteReader("select * from tbproductcategory where CoproductCategoryId={0}".FormatWith(productCategoryId)))
            {

                if (sdr.Read())
                {
                    parametersOne[2].Value = productCategoryId;
                    parametersOne[3].Value = sdr["CoCategoryPath"].SafeToString();
                }
                else
                {
                    throw new Exception("分类不存在");
                }
            }
            parametersOne[4].Value = model;
            parametersOne[5].Value = isRecommend;

            using (var sdr = DbHelperOleDb.ExecuteReader("select * from TbProductImage where CoProductImageId={0}".FormatWith(coverId)))
            {

                if (sdr.Read())
                {
                    parametersOne[6].Value = coverId;
                    parametersOne[7].Value = sdr["CoNormalImage"].SafeToString();
                    parametersOne[8].Value = sdr["CoSmallImage"].SafeToString();
                    parametersOne[9].Value = sdr["CoBigImage"].SafeToString();
                }
                else
                {
                    parametersOne[6].Value = DBNull.Value;
                    parametersOne[7].Value = DBNull.Value;
                    parametersOne[8].Value = DBNull.Value;
                    parametersOne[9].Value = DBNull.Value;
                }
            }
            parametersOne[10].Value = nameEn;
            parametersOne[11].Value = descriptionEn;
            parametersOne[12].Value = keyword;
            parametersOne[13].Value = finish;
            parametersOne[14].Value = material;
            parametersOne[15].Value = doorThickness;
            parametersOne[16].Value = backset;
            parametersOne[17].Value = availableFor;
            parametersOne[18].Value = applicableStandard;
            parametersOne[19].Value = keywordEn;
            parametersOne[20].Value = finishEn;
            parametersOne[21].Value = materialEn;
            parametersOne[22].Value = availableForEn;
            parametersOne[23].Value = size;
            parametersOne[24].Value = modelEn;
            parametersOne[25].Value = summary;
            parametersOne[26].Value = summaryEn;
            var obj = DbHelperOleDb.Add(strSqlOne.ToString(), parametersOne).SafeToInt();
            if (obj > 0)
            {
                DbHelperOleDb.ExecuteSqlTran(new ArrayList
                    {
                        "update TbProductImage set CoProductId={0} where CoProductImageId in ({1}) and  exists(select * from tbproduct where coproductid={0})".FormatWith(obj,string.Join(",",productImageIds.Select(o =>o.ToString(CultureInfo.InvariantCulture)).ToArray())),
                        "update TbProductImage set CoIsCover=1 where CoProductImageId={0}".FormatWith(coverId)

                    });
            }
            return obj;
        }
        public void Delete(int id)
        {
            var path = HttpContext.Current.Server.MapPath(ProductConfig.RelativePath);
            var ds = DbHelperOleDb.Query("select * from tbProductimage where CoProductId={0}".FormatWith(id));
            foreach (DataRow item in ds.Tables["ds"].Rows)
            {
                FileHelper.DeleteSub(Path.Combine(path, item["CoNormalImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoSmallImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoBigImage"].SafeToString()));
            }
            DbHelperOleDb.ExecuteSql("delete from TbProduct where CoProductId={0}".FormatWith(id));
            DbHelperOleDb.ExecuteSql("delete from tbProductimage where CoProductId={0}".FormatWith(id));
        }
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable(string name, string model, int productCategoryId, string isRecommend, int pageValue, int rowsValue, string sort = "CoDate", string order = "Desc")
        {
            var parameters = new[]
                             {
                                 new OleDbParameter("@CoName", OleDbType.VarChar, 255),
                              
                                 new OleDbParameter("@CoModel", OleDbType.VarChar, 255),
            
                                 new OleDbParameter("@CoProductCategoryId", OleDbType.Integer),
                                 new OleDbParameter("@CoIsRecommend",OleDbType.Boolean)
                             };
            parameters[0].Value = name.SqlLike();

            parameters[1].Value = model.SqlLike();

            parameters[2].Value = productCategoryId;
            parameters[3].Value = isRecommend.SafeToBool();
            var sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append(" where (A.CoName like '%'+@CoName+'%' or A.CoNameEn like '%'+@CoName+'%') and  (A.CoModel like '%'+@CoModel+'%'  or  A.CoModelEn like '%'+@CoModel+'%') ");
            if (productCategoryId != 0)
            {
                sbSqlWhere.Append(" and A.CoProductCategoryId=@CoProductCategoryId");
            }
            if (!isRecommend.IsNullOrEmpty())
            {
                sbSqlWhere.Append(" and A.CoIsRecommend=@CoIsRecommend");
            }
            int total = DbHelperOleDb.GetSingle("select count(*) from TbProduct AS A {0}".FormatWith(sbSqlWhere), parameters).SafeToInt();
            var sbSql = new StringBuilder();
            sbSql.AppendFormat("select top {0} A.CoProductId,A.CoName,A.CoSummary,A.CoSummaryEn,A.CoNameEn,A.CoDescription,A.CoDescriptionEn,A.CoProductCategoryId,A.CoIsRecommend,A.CoNormalImage,A.CoSmallImage, B.CoName as CoCategoryName from TbProduct as A left join TbProductCategory as B on  A.CoProductCategoryId=B.CoProductCategoryId {1} ", rowsValue, sbSqlWhere);
            if (pageValue > 1)
                sbSql.AppendFormat(" and A.CoProductId not in (select top {0} A.CoProductId from TbProduct AS A {1} order by A.{2} {3},  A.CoProductId ) ", rowsValue * (pageValue - 1), sbSqlWhere, sort, order);
            sbSql.AppendFormat(" order by A.{0} {1}, A.CoProductId Desc", sort, order);
            var ds = DbHelperOleDb.Query(sbSql.ToString(), parameters);
            var table = new Table
            {
                Rows = ds.Tables["ds"].Rows,
                Total = total,
                DataTable = ds.Tables["ds"]
            };
            return table;
        }
        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            var rows = DbHelperOleDb.Query("select *  from tbproduct  where CoProductId={0}".FormatWith(id)).Tables["ds"].Rows;
            return rows[0];
        }

    }
}
